import requests
import json
import time
from datetime import datetime
from django.conf import settings
from .models import Category, Product, ApiLog
import traceback
from sqlalchemy.dialects.mysql import insert
from sqlalchemy.orm import sessionmaker
from sqlalchemy import DateTime, Text, create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Boolean,Float,text,DateTime

Base = declarative_base()

class Category2(Base):
    __tablename__ = 'ebay_category'  # 表名和Django一致，方便同步
    __table_args__ = {'extend_existing': True}  # 关键！

    category_id = Column(String(32), primary_key=True)
    category_name = Column(String(255))
    parent_id = Column(String(32))
    level = Column(Integer)
    category_path = Column(String(1024))
    leaf_category = Column(Boolean)
    created_at = Column(DateTime, default=datetime.now)         
    updated_at = Column(DateTime, default=datetime.now, onupdate    =datetime.now)



class Product2(Base):
    """eBay商品模型"""
    __tablename__ = 'ebay_product'  # 表名和Django一致，方便同步
    __table_args__ = {'extend_existing': True}  # 关键！
    id = Column(Integer, primary_key=True)
    item_id = Column(String(32))
    title = Column(String(320))
    category_id =  Column(String(255))
    ebay_category_id =  Column(String(255))
    
    # 价格信息
    current_price =  Column(Float)
    currency = Column(String(30))
    
    # 商品信息
    condition = Column(String(30))
    listing_type = Column(String(30))
    buy_it_now_price = Column(Float)
    # 卖家信息
    seller_username = Column(String(30))
    seller_feedback_score =Column(Float)
    
    # 商品详情
    gallery_url =  Column(String(800))
    view_item_url =  Column(String(800))
    shipping_cost = Column(Float)
    location =  Column(String(30))
    
    # 时间信息
    start_time = Column(DateTime)
    end_time =  Column(DateTime)
    
    # 其他信息
    watch_count =  Column(Integer)
    bid_count =  Column(Integer)
    
    # 扩展字段 - 存储原始API返回的JSON数据
    raw_data =  Column(Text )
    
    created_at =  Column(DateTime)
    updated_at = Column(DateTime)
     
     


class EbayApiService:
    """eBay API服务类"""
    
    def __init__(self):
        # 您需要在Django settings中配置这些值
        self.app_id = getattr(settings, 'EBAY_APP_ID', '')
        self.cert_id = getattr(settings, 'EBAY_CERT_ID', '')
        self.dev_id = getattr(settings, 'EBAY_DEV_ID', '')
        self.auth_token = getattr(settings, 'EBAY_AUTH_TOKEN', '')
        
        # API端点
        self.taxonomy_base_url = 'https://api.ebay.com/commerce/taxonomy/v1'
        self.finding_base_url = 'https://svcs.ebay.com/services/search/FindingService/v1'
        
        # 默认市场ID (美国)
        self.marketplace_id = 'EBAY_US'
        
    def _log_api_call(self, api_type, operation, request_data=None, response_data=None, 
                     status='success', error_message=None, execution_time=None):
        """记录API调用日志"""
        try:
            ApiLog.objects.create(
                api_type=api_type,
                operation=operation,
                request_data=request_data,
                response_data=response_data,
                status=status,
                error_message=error_message,
                execution_time=execution_time
            )
        except Exception as e:
            print(f"日志记录失败: {e}")
    
    def get_default_category_tree_id(self, category_tree_id='0'):
        """获取默认的类目树ID"""
        start_time = time.time()
        
        try:
            if getattr(settings, 'EBAY_SANDBOX', True):  # 沙箱环境
                self.taxonomy_base_url = 'https://api.sandbox.ebay.com/commerce/taxonomy/v1'
                self.finding_base_url = 'https://svcs.sandbox.ebay.com/services/search/FindingService/v1'
            else:  # 生产环境
                self.taxonomy_base_url = 'https://api.ebay.com/commerce/taxonomy/v1'
                self.finding_base_url = 'https://svcs.ebay.com/services/search/FindingService/v1'
            url = f"{self.taxonomy_base_url}/category_tree/{category_tree_id}"
            headers = {
                'Authorization': f'Bearer {self.auth_token}',
                'Content-Type': 'application/json'
            }
            
            response = requests.get(url, headers=headers)
            execution_time = time.time() - start_time
            
            if response.status_code == 200:
                data = response.json()
                self._log_api_call(
                    'taxonomy', 'get_category_trees', 
                    request_data={'url': url},
                    response_data=data,
                    execution_time=execution_time
                )
                            
                stack = []
                root_node = data['rootCategoryNode']
                stack.append((root_node, 0, "", None))  # (当前节点, 层级, 路径, 父ID)

                category_objs = []
                category_ids = set()  # 防止重复

                while stack:
                    node, level, path, parent_id = stack.pop()
                    category = node.get('category', {})
                    category_id = category.get('categoryId')
                    category_name = category.get('categoryName')
                    current_path = f"{path}/{category_name}" if path else category_name
                    leaf_category = category.get('leafCategory', False)

                    if category_id and category_name and category_id not in category_ids:
                        category_objs.append(Category(
                            category_id=category_id,
                            category_name=category_name,
                            parent_id=parent_id,
                            level=level,
                            category_path=current_path,
                            leaf_category=leaf_category,
                        ))
                        category_ids.add(category_id)

                    children = node.get('childCategoryTreeNodes', [])
                    for child in reversed(children):
                        stack.append((child, level + 1, current_path, category_id))

                    # 批量插入，有则更新
                engine = create_engine(
                        "mysql+pymysql://root:Zb_200407@gz-cdb-d4j7h16x.sql.tencentcdb.com:22333/walmart?charset=utf8mb4"
                    )
                Session = sessionmaker(bind=engine)
                session = Session()
                category_dicts = [
                    # 这里填你的数据，每个是dict
                    {
                        'category_id': obj.category_id,
                        'category_name': obj.category_name,
                        'parent_id': obj.parent_id,
                        'level': obj.level,
                        'category_path': obj.category_path,
                        'leaf_category': obj.leaf_category,
                    }
                    for obj in category_objs
                ]

                stmt = insert(Category2).values(category_dicts)
                update_dict = {
                    'category_name': stmt.inserted.category_name,
                    'parent_id': stmt.inserted.parent_id,
                    'level': stmt.inserted.level,
                    'category_path': stmt.inserted.category_path,
                    'leaf_category': stmt.inserted.leaf_category,
                }
                on_duplicate_key_stmt = stmt.on_duplicate_key_update(**update_dict)

                session.execute(on_duplicate_key_stmt)
                session.commit()
                return '0'  # 默认返回0
            else:
                error_msg = f"获取类目树失败: {response.status_code} - {response.text}"
                self._log_api_call(
                    'taxonomy', 'get_category_trees',
                    request_data={'url': url},
                    status='error',
                    error_message=error_msg,
                    execution_time=execution_time
                )
                raise Exception(error_msg)
                
        except Exception as e:
            execution_time = time.time() - start_time
            self._log_api_call(
                'taxonomy', 'get_category_trees',
                status='error',
                error_message=str(e),
                execution_time=execution_time
            )
            raise e
    
    def get_category_tree(self, category_tree_id='0'):
        """获取完整的类目树"""
        start_time = time.time()
        
        try:
            url = f"{self.taxonomy_base_url}/category_tree/{category_tree_id}"
            headers = {
                'Authorization': f'Bearer {self.auth_token}',
                'Content-Type': 'application/json'
            }
            
            response = requests.get(url, headers=headers)
            execution_time = time.time() - start_time
            
            if response.status_code == 200:
                data = response.json()
                self._log_api_call(
                    'taxonomy', 'get_category_tree',
                    request_data={'category_tree_id': category_tree_id},
                    response_data=data,
                    execution_time=execution_time
                )
                return data
            elif response.status_code == 401:
                error_msg = "认证失败，请检查token"
                self._log_api_call(
                    'taxonomy', 'get_category_tree',
                    request_data={'category_tree_id': category_tree_id},
                    status='error',
                    error_message=error_msg,
                    execution_time=execution_time
                )
                raise Exception(error_msg)
            else:
                error_msg = f"获取类目树失败: {response.status_code} - {response.text}"
                self._log_api_call(
                    'taxonomy', 'get_category_tree',
                    request_data={'category_tree_id': category_tree_id},
                    status='error',
                    error_message=error_msg,
                    execution_time=execution_time
                )
                raise Exception(error_msg)
                
        except Exception as e:
            execution_time = time.time() - start_time
            self._log_api_call(
                'taxonomy', 'get_category_tree',
                request_data={'category_tree_id': category_tree_id},
                status='error',
                error_message=str(e),
                execution_time=execution_time
            )
            raise e
    
    def save_categories(self, categories_data, parent_id=None, level=1, path=""):
        """保存类目数据到数据库"""
        saved_count = 0
        
        for category_data in categories_data:
            category_id = category_data.get('categoryId')
            category_name = category_data.get('categoryName')
            
            if not category_id or not category_name:
                continue
                
            current_path = f"{path}/{category_name}" if path else category_name
            
            # 检查是否为叶子类目
            leaf_category = category_data.get('leafCategory', False)
            
            # 创建或更新类目
            category, created = Category.objects.update_or_create(
                category_id=category_id,
                defaults={
                    'category_name': category_name,
                    'parent_id': parent_id,
                    'level': level,
                    'leaf_category': leaf_category,
                    'category_path': current_path,
                }
            )
            
            if created:
                saved_count += 1
                print(f"创建类目: {category_name} (ID: {category_id}, Level: {level})")
            
            # 处理子类目
            child_categories = category_data.get('childCategoryTreeNodes', [])
            if child_categories:
                child_count = self.save_categories(
                    child_categories, 
                    parent_id=category_id, 
                    level=level + 1,
                    path=current_path
                )
                saved_count += child_count
        
        return saved_count
    
    def sync_all_categories(self):
        """同步所有类目"""
        try:
            print("开始同步eBay类目...")
            
            # 获取类目树ID
            category_tree_id = self.get_default_category_tree_id()
            print(f"使用类目树ID: {category_tree_id}")
            
            # 获取类目树数据
            tree_data = self.get_category_tree(category_tree_id)
            
            # 解析并保存类目
            root_categories = tree_data.get('rootCategoryNode', {}).get('childCategoryTreeNodes', [])
            saved_count = self.save_categories(root_categories)
            
            print(f"类目同步完成，共保存 {saved_count} 个类目")
            return saved_count
            
        except Exception as e:
            print(f"类目同步失败: {e}")
            traceback.print_exc()
            raise e
    
    def find_items_by_category(self, category_id, entries_per_page=100, page_number=1):
        """根据类目ID查找商品"""
        start_time = time.time()
        
        try:
            url = self.finding_base_url
            if getattr(settings, 'EBAY_SANDBOX', True):
                url='https://svcs.sandbox.ebay.com/services/search/FindingService/v1'
            # category_id=11104
            # 构建请求参数
            params = {
                'OPERATION-NAME': 'findItemsByCategory',
                'SERVICE-VERSION': '1.0.0',
                'SECURITY-APPNAME': self.app_id,
                'RESPONSE-DATA-FORMAT': 'JSON',
                'REST-PAYLOAD': '',
                'categoryId': category_id,
                'paginationInput.entriesPerPage': min(entries_per_page, 100),  # 最大100
                'paginationInput.pageNumber': page_number,
                'sortOrder': 'BestMatch',
            }
            
            response = requests.get(url, params=params)
            execution_time = time.time() - start_time
            
            if response.status_code == 200:
                data = response.json()
                self._log_api_call(
                    'finding', 'findItemsByCategory',
                    request_data={'category_id': category_id, 'page': page_number},
                    response_data=data,
                    execution_time=execution_time
                )
                return data
            else:
                error_msg = f"查找商品失败: {response.status_code} - {response.text}"
                self._log_api_call(
                    'finding', 'findItemsByCategory',
                    request_data={'category_id': category_id, 'page': page_number},
                    status='error',
                    error_message=error_msg,
                    execution_time=execution_time
                )
                raise Exception(error_msg)
                
        except Exception as e:
            execution_time = time.time() - start_time
            self._log_api_call(
                'finding', 'findItemsByCategory',
                request_data={'category_id': category_id, 'page': page_number},
                status='error',
                error_message=str(e),
                execution_time=execution_time
            )
            traceback.print_exc()
            raise e
    
    def save_products(self, items_data, category_id):
        """保存商品数据到数据库"""
        saved_count = 0
        
        try:
            category = Category.objects.get(category_id=category_id)
        except Category.DoesNotExist:
            print(f"类目 {category_id} 不存在")
            return 0
        product_dicts=[]
        for item in items_data:
            try:
                item_id = item.get('itemId', [''])[0] if isinstance(item.get('itemId'), list) else item.get('itemId', '')
                
                if not item_id:
                    continue
                
                # 解析价格信息
                selling_status = item.get('sellingStatus', [{}])[0] if isinstance(item.get('sellingStatus'), list) else item.get('sellingStatus', {})
                current_price_data = selling_status.get('currentPrice', [{}])[0] if isinstance(selling_status.get('currentPrice'), list) else selling_status.get('currentPrice', {})
                current_price = float(current_price_data.get('__value__', 0)) if current_price_data.get('__value__') else None
                currency = current_price_data.get('@currencyId', 'USD')
                
                # 解析其他信息
                title = item.get('title', [''])[0] if isinstance(item.get('title'), list) else item.get('title', '')
                condition = item.get('condition', [{}])[0].get('conditionDisplayName', [''])[0] if item.get('condition') else ''
                listing_info = item.get('listingInfo', [{}])[0] if isinstance(item.get('listingInfo'), list) else item.get('listingInfo', {})
                listing_type = listing_info.get('listingType', [''])[0] if isinstance(listing_info.get('listingType'), list) else listing_info.get('listingType', '')
                
                # 解析一口价
                buy_it_now_price = None
                if listing_info.get('buyItNowPrice'):
                    bin_price_data = listing_info['buyItNowPrice'][0] if isinstance(listing_info['buyItNowPrice'], list) else listing_info['buyItNowPrice']
                    buy_it_now_price = float(bin_price_data.get('__value__', 0)) if bin_price_data.get('__value__') else None
                
                # 解析卖家信息
                seller_info = item.get('sellerInfo', [{}])[0] if isinstance(item.get('sellerInfo'), list) else item.get('sellerInfo', {})
                seller_username = seller_info.get('sellerUserName', [''])[0] if isinstance(seller_info.get('sellerUserName'), list) else seller_info.get('sellerUserName', '')
                seller_feedback_score = int(seller_info.get('feedbackScore', ['0'])[0]) if seller_info.get('feedbackScore') else None
                
                # 解析图片和链接
                gallery_url = item.get('galleryURL', [''])[0] if isinstance(item.get('galleryURL'), list) else item.get('galleryURL', '')
                view_item_url = item.get('viewItemURL', [''])[0] if isinstance(item.get('viewItemURL'), list) else item.get('viewItemURL', '')
                
                # 解析运费
                shipping_info = item.get('shippingInfo', [{}])[0] if isinstance(item.get('shippingInfo'), list) else item.get('shippingInfo', {})
                shipping_cost_data = shipping_info.get('shippingServiceCost', [{}])[0] if shipping_info.get('shippingServiceCost') else {}
                shipping_cost = float(shipping_cost_data.get('__value__', 0)) if shipping_cost_data.get('__value__') else None
                
                # 解析位置
                location = item.get('location', [''])[0] if isinstance(item.get('location'), list) else item.get('location', '')
                
                # 解析时间
                start_time = listing_info.get('startTime', [''])[0] if listing_info.get('startTime') else ''
                end_time = listing_info.get('endTime', [''])[0] if listing_info.get('endTime') else ''
                
                # 转换时间格式
                start_time_obj = None
                end_time_obj = None
                try:
                    if start_time:
                        start_time_obj = datetime.fromisoformat(start_time.replace('Z', '+00:00'))
                    if end_time:
                        end_time_obj = datetime.fromisoformat(end_time.replace('Z', '+00:00'))
                except:
                    pass
                
                # 创建或更新商品
 
                product_dict={
                        "item_id":item_id,
                        'title': title,
                        'category_id': category.id,
                        'ebay_category_id': category_id,
                        'current_price': current_price,
                        'currency': currency,
                        'condition': condition,
                        'listing_type': listing_type,
                        'buy_it_now_price': buy_it_now_price,
                        'seller_username': seller_username,
                        'seller_feedback_score': seller_feedback_score,
                        'gallery_url': gallery_url,
                        'view_item_url': view_item_url,
                        'shipping_cost': shipping_cost,
                        'location': location,
                        'start_time': start_time_obj,
                        'end_time': end_time_obj,
                        'raw_data': json.dumps(item) if isinstance(item, dict) else str(item),
                        'created_at': datetime.now(),
                        'updated_at': datetime.now()
                    }
                product_dicts.append(product_dict)
                
  
                    
            except Exception as e:
                print(f"保存商品失败 {item_id}: {e}")
                continue
        try:
            engine = create_engine(           "mysql+pymysql://root:Zb_200407@gz-cdb-d4j7h16x.sql.tencentcdb.com:22333/walmart?charset=utf8mb4"       )
            Session = sessionmaker(bind=engine)
            session = Session()        
            stmt = insert(Product2).values(product_dicts)
            update_dict = {
                "item_id": stmt.inserted.item_id,
                'title': stmt.inserted.title,
                'category_id': stmt.inserted.category_id,
                'ebay_category_id': stmt.inserted.ebay_category_id,
                'current_price': stmt.inserted.current_price,
                'currency': stmt.inserted.currency,
                'condition': stmt.inserted.condition,
                'listing_type': stmt.inserted.listing_type,
                'buy_it_now_price': stmt.inserted.buy_it_now_price,
                'seller_username': stmt.inserted.seller_username,
                'seller_feedback_score': stmt.inserted.seller_feedback_score,
                'gallery_url': stmt.inserted.gallery_url,
                'view_item_url': stmt.inserted.view_item_url,
                'shipping_cost': stmt.inserted.shipping_cost,
                'location': stmt.inserted.location,
                'start_time': stmt.inserted.start_time,
                'end_time': stmt.inserted.end_time,
                'raw_data': stmt.inserted.raw_data,
                'updated_at': datetime.now()
            }
            on_duplicate_key_stmt = stmt.on_duplicate_key_update(**update_dict)

            # 4. 执行批量 upsert
            session.execute(on_duplicate_key_stmt)
            session.commit()
        except Exception as e:
            traceback.print_exc()
        return saved_count
    
    def sync_products_by_category(self, category_id, max_items=10000):
        """同步指定类目下的商品"""
        try:
            print(f"开始同步类目 {category_id} 下的商品...")
            
            total_saved = 0
            page_number = 1
            entries_per_page = 100
            
            while total_saved < max_items:
                print(f"正在获取第 {page_number} 页数据...")
                
                # 获取商品数据
                result = self.find_items_by_category(category_id, entries_per_page, page_number)
                
                # 解析响应
                find_items_response = result.get('findItemsByCategoryResponse', [{}])[0]
                search_result = find_items_response.get('searchResult', [{}])[0]
                items = search_result.get('item', [])
                
                if not items:
                    print("没有更多商品数据")
                    break
                
                # 保存商品
                saved_count = self.save_products(items, category_id)
                total_saved += saved_count
                
                print(f"第 {page_number} 页保存了 {saved_count} 个商品，累计 {total_saved} 个")
                
                # 检查是否还有更多页面
                pagination_output = find_items_response.get('paginationOutput', [{}])[0]
                total_pages = int(pagination_output.get('totalPages', ['0'])[0])
                
                if page_number >= total_pages or total_saved >= max_items:
                    break
                
                page_number += 1
                time.sleep(1)  # 避免请求过快
            
            print(f"类目 {category_id} 商品同步完成，共保存 {total_saved} 个商品")
            return total_saved
            
        except Exception as e:
            print(f"商品同步失败: {e}")
            traceback.print_exc()
            raise e 